package com.gscitysfy.cus.drain.config;

import com.alibaba.cloud.commons.lang.StringUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.util.*;

/**
 * sql查询拦截器
 * <p>
 * 模糊查询时过滤特殊字符
 * </p>
 * @author 刘少华
 * @version 1.0
 * @date 2022/5/10 11:13
 */
@Slf4j
@Component
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class FuzzyQueryInterceptor implements Interceptor{
    /**
     * SQL语句like
     */
    private final static String SQL_LIKE = " like ";

    /**
     * PgSql忽略大小写ilike
     */
    private final static String SQL_ILIKE = " ilike ";

    /**
     * SQL语句占位符
     */
    private final static String SQL_PLACEHOLDER = "?";

    /**
     * SQL语句占位符分隔
     */
    private final static String SQL_PLACEHOLDER_REGEX = "\\?";

    private final static String FUZZY_QUERY_KEY = "%";

    /**
     * 所有的转义器
     */
    private static Map<Class, AbstractFuzzyQuerySqlConverter> converterMap = new HashMap<>(4);

    static {
        converterMap.put(Map.class, new MapFuzzyQuerySqlConverter());
        converterMap.put(Object.class,new ObjectFuzzyQuerySqlConverter());
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        //拦截StatementHandler，获取Mybatis对象属性
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        MetaObject metaObject = MetaObject
                .forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY,
                        new DefaultReflectorFactory());
        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
        //只拦截查询语句，对特殊字符“%,_,\\”进行处理
        if (SqlCommandType.SELECT.equals(mappedStatement.getSqlCommandType())){
            BoundSql boundSql = statementHandler.getBoundSql();
            String sql = boundSql.getSql();
            Object parameterObject = statementHandler.getBoundSql().getParameterObject();
            transferLikeSql(sql, parameterObject, boundSql);
        }
        //执行结果
        return invocation.proceed();
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {
    }

    /**
     * 修改包含like的SQL语句
     * @param sql             SQL语句
     * @param parameterObject 参数对象
     * @param boundSql        绑定SQL对象
     */
    private void transferLikeSql(String sql, Object parameterObject, BoundSql boundSql) {
        if (!isEscape(sql)) {
            return;
        }
        //将模糊查询中like关键字替换为ilike，以忽略大小写
        if (hasNotIlike(sql)){
            sql = sql.contains(SQL_LIKE) ? sql.replace(SQL_LIKE,SQL_ILIKE) : sql.replace(SQL_LIKE.toUpperCase(),SQL_ILIKE.toUpperCase());
            try {
                Field field = boundSql.getClass().getDeclaredField("sql");
                field.setAccessible(true);
                field.set(boundSql, sql);
            }catch(NoSuchFieldException  | IllegalAccessException e){
                e.printStackTrace();
            }
        }
        //模糊查询参数不通过占位符拼接在SQL语句中，处理后直接放行
        if (isAppendFuzzyQuery(sql)){
            try{
                transferAppendLike(sql,boundSql);
            }catch(Throwable ex){
                ex.printStackTrace();
            }
        }else {
            sql = sql.replaceAll(" {2}", " ");
            // 获取关键字的个数（去重）
            Set<String> fields = this.getKeyFields(sql, boundSql);
            if (fields == null) {
                return;
            }
            AbstractFuzzyQuerySqlConverter converter;
            if (parameterObject instanceof Map) {
                converter = converterMap.get(Map.class);
            } else {
                converter = converterMap.get(Object.class);
            }
            converter.convert(sql, fields, parameterObject);
        }

    }

    /**
     * 判断是否未采用占位符直接拼接的like查询sql
     * @param sql
     * @return
     */
    private boolean isAppendFuzzyQuery(String sql){
        //判断是否为mybatis-plus中like的SQL语句样式
        if (sql.toLowerCase().contains("ilike ?")){
            return false;
        }
        int begIndex = sql.indexOf(FUZZY_QUERY_KEY);
        int endIndex = 0;
        if (getCount(sql,FUZZY_QUERY_KEY) > 1){
            endIndex = org.apache.commons.lang3.StringUtils.ordinalIndexOf(sql,FUZZY_QUERY_KEY,2);
        }
        String str = endIndex >0 ? sql.substring(begIndex+1,endIndex) : "";
        return (!str.contains(SQL_PLACEHOLDER));
    }

    /**
     * 判断sql中是否只包含like且不包含ilike关键字
     * @param sql
     * @return
     */
    private boolean hasNotIlike(String sql){
        if (StringUtils.isBlank(sql)) {
            return false;
        }
        return (sql.toLowerCase().contains(SQL_LIKE) && !sql.toLowerCase().contains(SQL_ILIKE));
    }

    /**
     * 替换直接拼接的模糊查询中的特殊字符
     * @param sql
     * @param boundSql
     * @throws Throwable
     */
    private void transferAppendLike (String sql,BoundSql boundSql) throws Throwable{
        sql = dealAppendSql(sql,getCount(sql.toLowerCase(),SQL_ILIKE));
        //通过反射修改sql语句
        Field field = boundSql.getClass().getDeclaredField("sql");
        field.setAccessible(true);
        field.set(boundSql, sql);
    }

    /**
     * 处理直接拼接的模糊查询中的特殊字符
     * @param sql 待执行sql语句
     * @param keyCount ilkie关键字出现次数
     * @return 处理后的sql语句
     */
    private String dealAppendSql(String sql,int keyCount){
        //判断有几个ilike关键字，有几个就循环几次
        for (int i = 1;i<= keyCount;i++){
            String text;
            //取出每个ilike拼接的参数字符串，注意最后一个直接截取到末尾，其他截取两个ilike之间的字符串
            if (i <= keyCount -1){
                text = sql.substring(org.apache.commons.lang3.StringUtils.ordinalIndexOf(sql.toLowerCase(),SQL_ILIKE,i)
                        ,org.apache.commons.lang3.StringUtils.ordinalIndexOf(sql.toLowerCase(),SQL_ILIKE,i+1));
            }else{
                text = sql.substring(org.apache.commons.lang3.StringUtils.ordinalIndexOf(sql.toLowerCase(),SQL_ILIKE,i));
            }
            //获取每个用单引号拼接的目标参数
            String goalStr = text.substring(org.apache.commons.lang3.StringUtils.ordinalIndexOf(text,"'",1)+1,
                    org.apache.commons.lang3.StringUtils.ordinalIndexOf(text,"'",2));
            StringBuilder goalBuilder = new StringBuilder(goalStr);
            int keyIndex;
            if (getCount(goalStr,FUZZY_QUERY_KEY) > 2){
                keyIndex = org.apache.commons.lang3.StringUtils.ordinalIndexOf(goalStr,FUZZY_QUERY_KEY,2);
                goalBuilder.replace(keyIndex,keyIndex+1,"\\\\%");
            }else if(goalStr.contains("_")) {
                keyIndex = goalStr.indexOf("_");
                goalBuilder.replace(keyIndex,keyIndex+1,"\\\\_");
            } else if (goalStr.contains("\\")){
                keyIndex = goalStr.indexOf("\\");
                goalBuilder.replace(keyIndex,keyIndex+1,"\\\\");
            }
            sql = sql.replaceAll(goalStr,goalBuilder.toString());
        }
        return sql;
    }
    /**
     * 获取目标字符串在指定字符串中出现的次数
     * @param str 指定字符串
     * @param goalStr 目标字符串
     * @return 字符串出现次数
     */
    private int getCount(String str,String goalStr) {
        int count = 0;
        while(str.contains(goalStr)) {
            str = str.substring(str.indexOf(goalStr)+goalStr.length());
            count++;
        }
        return count;
    }

    /**
     * 是否需要转义
     * @param sql SQL语句
     * @return true/false
     */
    private boolean isEscape(String sql) {
        return this.hasLike(sql);
    }

    /**
     * 判断SQL语句中是否含有like关键字
     * @param str SQL语句
     * @return true/false
     */
    private boolean hasLike(String str) {
        if (StringUtils.isBlank(str)) {
            return false;
        }
        return str.toLowerCase().contains(SQL_LIKE) || str.toLowerCase().contains(SQL_ILIKE);
    }

    /**
     * 获取需要替换的所有字段集合
     * @param sql      完整SQL语句
     * @param boundSql 绑定的SQL对象
     * @return 字段集合列表
     */
    private Set<String> getKeyFields(String sql, BoundSql boundSql) {
        String[] params = sql.split(SQL_PLACEHOLDER_REGEX);
        Set<String> fields = new HashSet<>();
        for (int i = 0; i < params.length; i++) {
            if (this.hasLike(params[i])) {
                String field = boundSql.getParameterMappings().get(i).getProperty();
                fields.add(field);
            }
        }
        return fields;
    }

}
